package nuc.ss.wl.dao.impl;

import nuc.ss.wl.dao.SCDao;
import nuc.ss.wl.entity.SC;
import nuc.ss.wl.entity.S_SC_C;
import nuc.ss.wl.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class SCDaoImpl implements SCDao {

    @Override
    public int save(SC sc) {
        String sql = "insert into SC values(?, ?, ?)";
        Object[] params = {
            sc.getSno(),
            sc.getCno(),
            sc.getGrade()
        };
        return DBUtil.executeUpdate(sql, params);
    }

    @Override
    public int delete(int sno, int cno) {
        String sql = "delete from SC where sno = ? and cno = ?";
        Object[] params = {
                sno,
                cno
        };
        return DBUtil.executeUpdate(sql, params);
    }

    @Override
    public int update(SC sc) {
        String sql = "update SC set grade = ? where sno = ? and cno = ?";
        Object[] params = {
                sc.getGrade(),
                sc.getSno(),
                sc.getCno()
        };
        return DBUtil.executeUpdate(sql, params);
    }

    @Override
    public List<S_SC_C> findAll(int cno) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        S_SC_C s_sc_c = null;
        List<S_SC_C> s_sc_cList = new ArrayList<>();
        conn = DBUtil.getConnection();
        String sql = "select sno, cno, classno, sname, cname, grade from `s_sc_c` where cno = ? ORDER BY grade desc;";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, cno);
            rs = ps.executeQuery();
            while (rs.next()) {
                s_sc_c = new S_SC_C(rs.getInt(1),
                        rs.getInt(2),
                        rs.getInt(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getFloat(6)
                        );
                s_sc_cList.add(s_sc_c);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(rs, ps, conn);
        }
        return s_sc_cList;
    }

    @Override
    public SC findBySnoAndCno(int sno, int cno) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        SC sc = null;
        List<SC> scList = new ArrayList<>();
        conn = DBUtil.getConnection();
        String sql = "select * from SC where sno = ? and cno = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, sno);
            ps.setInt(2, cno);
            rs = ps.executeQuery();
            while (rs.next()) {
                sc = new SC(rs.getInt(1), rs.getInt(2), rs.getFloat(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(rs, ps, conn);
        }
        return sc;
    }
}
